<?php

/*
 * @copyright:      Ienter asia Technologies
 * @author:         Quyet
 * @version:        v.1.0
 * @created:        11/6/2014
 * @Description:    Class model List Time Card
 */

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Mmod_listtimecard extends MYAD_Model {

    protected $table_name = 'timecard';
    protected $table_employee = 'm_employee';
    protected $table_employeesalaryinfo = 'm_employeesalaryinfo';
    protected $table_employeepitinfo = 'm_employeepitinfo';
    protected $table_employeeleaveinfo = 'm_employeeleaveinfo';
    protected $table_employeebonus = 'm_employeebonus';
    protected $table_leaveregistration = 'm_leaveregistration';
    protected $table_leavetype = 'm_leavetype';
    protected $table_shiftinfo = 'shiftinfo';
    protected $table_salaryparameter = 'm_salaryparameter';
    protected $table_currencyconversionrate = 'm_currencyconversionrate';
    protected $table_pitparameter = 'm_pitparameter';
    protected $table_pitcalculationrule = 'm_pitcalculationrule';
    protected $table_holiday = 'm_holiday';
    protected $table_payroll = 'payroll';

    function __construct() {
        parent::__construct();
        $this->load->driver('cache', array('adapter' => 'apc', 'backup' => 'file'));
    }

    /*
     * @author :        Quyet
     * @created:        11/6/2014
     * @Description:    Get all data for view of list time card
     * @Input :         Null
     * @Output :        (Array) data for view data
     */

    function getAllData($page = 0) {
        /* load all label */
        $labelData = $this->getLabelData();

        /* load all employee data */
        $condition = array('count' => TRUE);
        $allEmployeeData = $this->getEmployee($condition);

        /* load pagination */
        $pagiData = array(
            'base_url' => base_url('listtimecard/'),
            'total_rows' => $allEmployeeData,
        );

        $pagi = $this->getPagination($pagiData);

        /* load employee data range */
        $pagination = $this->config->item('pagination');
        $offset = isset($pagiData['per_page']) ? $pagiData['per_page'] : $pagination['per_page'];

        $condition = array('limit' => array($page, $offset));
        $employeeData['data'] = $this->setMaskEmployee($condition);

        /* Load view page */
        $emptyMessage = $this->lang->line('ltc_table_empty');
        $data = array(
            'show' => 'index',
            'pagi' => $pagi,
            'uDate' => $this->monthForSalary,
            'emptyMessage' => (empty($emptyMessage) ? 'Empty data ' : $emptyMessage),
            'page' => $page,
            'minYear' => $this->session->userdata('minYear'),
            'maxYear' => $this->session->userdata('maxYear'),
            'cmbMonth' => $this->session->userdata('cmbMonth'),
            'cmbYear' => $this->session->userdata('cmbYear'),
        );
        return array_merge($data, $labelData, $employeeData);
    }

    /*
     * @author :        Quyet
     * @created:        11/6/2014
     * @Description:    Get label data of list time card
     * @Input :         void
     * @Output :        (Array) data label
     */

    function getLabelData() {

        $title = $this->lang->line('ltc_title');
        $table = $this->lang->line('ltc_table');
        $lblMonth = $this->lang->line('ltc_lblMonth');
        $lblYear = $this->lang->line('ltc_lblYear');
        $lblSearch = $this->lang->line('ltc_lblSearch');
        $table_col_order = $this->lang->line('ltc_table_col_order');
        $table_col_fullName = $this->lang->line('ltc_table_col_fullName');
        $table_col_email = $this->lang->line('ltc_table_col_email');
        $table_col_actualWorkHour = $this->lang->line('ltc_table_col_actualWorkHour');
        $button_salaryCalculation = $this->lang->line('ltc_button_salaryCalculation');
        $data = array(
            'title' => (empty($title) ? 'List Time Card' : $title),
            'table' => (empty($table) ? '' : $table),
            'lblMonth' => (empty($lblMonth) ? 'Month' : $lblMonth),
            'lblYear' => (empty($lblYear) ? 'Year' : $lblYear),
            'lblSearch' => (empty($lblSearch) ? 'Search' : $lblSearch),
            'table_col_order' => (empty($table_col_order) ? 'Order' : $table_col_order),
            'table_col_fullName' => (empty($table_col_fullName) ? 'Full name' : $table_col_fullName),
            'table_col_email' => (empty($table_col_email) ? 'Email' : $table_col_email),
            'table_col_actualWorkHour' => (empty($table_col_actualWorkHour) ? 'Actual Work Hours' : $table_col_actualWorkHour),
            'button_salaryCalculation' => (empty($button_salaryCalculation) ? 'Salary Calculation' : $button_salaryCalculation),
        );
        return $data;
    }

    /* ------------------------------------------------------------------------ */
    /*
     * @author :        Quyet
     * @created:        2014/8/19
     * @Description:    get employee for salary caculation
     * @Input :         (Array) $condition
     * @Output :        Array(object) Employee
     */

    function setCacheData() {
        $companyCode = $this->session->userdata('CompanyCode');
        $condition = array(
            'CompanyCode' => $companyCode,
        );
        $ltc_employees = $this->getEmployee();
        $ltc_conversion_rate = $this->getConversionSParametersPParameters($this->table_currencyconversionrate, $condition);
        $ltc_salary_parameter = $this->getConversionSParametersPParameters($this->table_salaryparameter, $condition);
        $ltc_pit_parameter = $this->getConversionSParametersPParameters($this->table_pitparameter, $condition);
        $ltc_pit_rule = $this->getPITRules();
        $ltc_salary_info = $this->getSalaryBonusDependant('EmployeeId, GrossSalary', $this->table_employeesalaryinfo);
        $ltc_bonus_info = $this->getSalaryBonusDependant('EmployeeId, Bonus', $this->table_employeebonus);
        $ltc_dependant_info = $this->getSalaryBonusDependant('EmployeeId, NumberOfDependants', $this->table_employeepitinfo);
        $ltc_special_leave = $this->getSpecialLeave();

        $dataCache = array(
            'ltc_employees' => $ltc_employees,
            'ltc_conversion_rate' => $ltc_conversion_rate,
            'ltc_salary_parameter' => $ltc_salary_parameter,
            'ltc_pit_parameter' => $ltc_pit_parameter,
            'ltc_pit_rule' => $ltc_pit_rule,
            'ltc_salary_info' => $ltc_salary_info,
            'ltc_bonus_info' => $ltc_bonus_info,
            'ltc_dependant_info' => $ltc_dependant_info,
            'ltc_special_leave' => $ltc_special_leave,
        );
        $time = ($this->config->item('cachetime')) ? $this->config->item('cachetime') : 300;

        foreach ($dataCache as $key => $value) {
            $this->cache->file->save($key . '_' . $companyCode, $value, $time);
        }
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/19
     * @Description:    get employee for salary caculation
     * @Input :         (Array) $condition
     * @Output :        Array(object) Employee
     */

    function getEmployee($condition = array()) {
        $select = 'a.EmployeeId, a.EmployeeName, a.Email, a.EmployeeType, a.startProbationaryDate, a.endProbationaryDate, a.startOfficialDate, a.endOfficialDate'
                . ', SUM(b.WeekdayNormalTime) as ActualAttendant'
                . ', SUM(b.WeekdayOvertime) as WeekdayOvertime'
                . ', SUM(b.WeekdayLateOvertime) as WeekdayLateOvertime'
                . ', SUM(b.WeekendOvertime) as WeekendOvertime'
                . ', SUM(b.WeekendLateOvertime) as WeekendLateOvertime'
                . ', SUM(b.HolidayOvertime) as HolidayOvertime'
                . ', SUM(b.HolidayLateOvertime) as HolidayLateOvertime'
                . ', SUM(b.LateArrival) as LateArrivalTime'
                . ', SUM(b.EarlyLeave) as EarlyLeaveTime'
                . ', SUM(b.ActualWorkHours) as sumActualWorkHours'
                . ', SUM(b.LeaveIsPaid) as LeaveIsPaid'
                . ', SUM(b.LeaveNoPaid) as LeaveNoPaid'
                . ', SUM(b.TimeWorking) as TimeWorking';
        $condition['select'] = $select;
        $SQL = "((startProbationaryDate <= '" . date('Y-m-d', $this->monthForSalary) . "' AND endProbationaryDate >= '" . date('Y-m-d', $this->monthForSalary) . "')";
        $SQL .= " OR (startOfficialDate <= '" . date('Y-m-d', $this->monthForSalary) . "' AND endOfficialDate ='0000-00-00' AND startOfficialDate !='0000-00-00')";
        $SQL .= " OR (startOfficialDate <= '" . date('Y-m-d', $this->monthForSalary) . "' AND endOfficialDate >= '" . date('Y-m-d', $this->monthForSalary) . "'))";
        $SQL .= " AND MONTH(date)=" . date('m', $this->monthForSalary);
        $SQL .= " AND YEAR(date)=" . date('Y', $this->monthForSalary);
        $SQL .= " AND CompanyCode='" . $this->session->userdata('CompanyCode') . "'";
        $SQL .= " AND Status= 1";
        $SQL .= " AND a.Del_Flag = 0";
        $condition['where'] = $SQL;
        $condition['join'] = array(
            array('table' => $this->table_name . ' as b', 'condition' => 'a.EmployeeId = b.EmployeeId', 'type' => 'inner'),
        );
        $condition['group_by'] = array('a.EmployeeId');
        $condition['disable_del'] = TRUE;

        $result = $this->selectDB($this->table_employee . ' as a', $condition);
        if (is_array($result)) {
            $temp = array();
            foreach ($result as $item) {
                $temp[$item->EmployeeId] = $item;
            }
            $result = $temp;
        }
        return $result;
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/19
     * @Description:    mask Employee if ActualArrivalTime=ActualLeaveTime of not found getBasicSalary
     * @Input :         (Array) $employeeID
     * @Output :        Boolean
     */

    function maskEmployee($employeeID = 0) {
        $flag = 0;
        $message = NULL;
        $basicSalary = FALSE;
        /* BEGIN - Check ActualArrivalTime = ActualLeaveTime */
        $condition = array(
            'where' => array(
                'EmployeeId' => (integer) $employeeID,
                'MONTH(Date)' => date('m', $this->monthForSalary),
                'YEAR(Date)' => date('Y', $this->monthForSalary),
            ),
            'count' => TRUE,
            'disable_del' => TRUE
        );
        $this->db->where('`ActualArrivalTime` = `ActualLeaveTime`');
        $flag_ = $this->selectDB($this->table_name, $condition);
        if ($flag_) {
            $flag = 1;
            $message .= $this->lang->line('ltc_button_salaryCalculation_active_actual_arrival_time_euqa_actual_leave_time') . ', ';
        }
        /* END - Check ActualArrivalTime = ActualLeaveTime */

        /* BEGIN - find basic salary info */
        $basicSalarys = $this->getSalaryBonusDependant('EmployeeId, GrossSalary', $this->table_employeesalaryinfo);
        $basicSalary = isset($basicSalarys[$employeeID]->GrossSalary) ? $basicSalarys[$employeeID]->GrossSalary : 0;
        if (!$basicSalary) {
            $flag = 2;
            $message .= $this->lang->line('ltc_button_salaryCalculation_active_not_basic_salarys_info');
        }
        /* END - Find basic salary info */
        return array(
            'flag' => $flag,
            'message' => $message,
            'basicSalary' => $basicSalary
        );
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/19
     * @Description:    set mask for list employee
     * @Input :         (Array) $employeeID
     * @Output :        (Array)
     */

    function setMaskEmployee($condition = array()) {
        $temp = array();
        $employees = $this->getEmployee($condition);
        if ($employees) {
            foreach ($employees as $key => $item) {
                $temp[$key] = $item;
                $result = $this->maskEmployee($item->EmployeeId);
                if ($result['flag']) {
                    $temp[$key]->maskError = $result['flag'];
                    $temp[$key]->maskMessage = $result['message'];
                } else {
                    $temp[$key]->maskError = FALSE;
                    $temp[$key]->maskMessage = '';
                }
            }
        }
        return $temp;
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/20
     * @Description:    get data for caculation
     * @Input :         (string) $table //table name
     *                  (array) $condition // conditon query
     * @Output :
     */

    function getDataForCaculation($table = '', $condition = array()) {
        $condition['ApplyDate <='] = date('Y-m-d', $this->monthForSalary);
        $condition['Del_Flag'] = FALSE;
        $condition = array(
            'where' => $condition,
            'first' => TRUE,
            'order_by' => 'ApplyDate DESC',
            'disable_del' => TRUE
        );
        return $this->selectDB($table, $condition);
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/20
     * @Description:    get Conversion Rate, SalaryParameter, PITParameters
     * @Input :         void
     * @Output :        (object)/false
     */

    function getConversionSParametersPParameters($table = '', $condition = array()) {
        return $this->getDataForCaculation($table, $condition);
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/20
     * @Description:    get PITParameters
     * @Input :         void
     * @Output :        (object)/false
     */

    function getPITRules() {
        $temp = FALSE;
        $companyCode = $this->session->userdata('CompanyCode');
        $condition = array(
            'CompanyCode' => $companyCode,
        );
        $PITParameters = $this->getConversionSParametersPParameters($this->table_pitparameter, $condition);
        if ($PITParameters) {
            $condition = array(
                'where' => array(
                    'ParameterId' => $PITParameters->ParameterId,
                    'Del_Flag' => FALSE
                ),
                'order_by' => 'AssociatedAmount ASC',
                'disable_del' => TRUE
            );
            $temp = $this->selectDB($this->table_pitcalculationrule, $condition);
        }

        return $temp;
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/21
     * @Description:    get basic salary, bonus, depandent, for caculation
     * @Input :         void
     * @Output :        (object)/false
     */

    function getSalaryBonusDependant($select = '', $tableName = '') {
        $employee = $this->cache->file->get('ltc_employees');
        $temp = FALSE;
        if (!$employee) {
            $employee = $this->getEmployee();
        }

        if ($employee) {
            $employeeID = array();
            foreach ($employee as $item) {
                /* push employee ID to array */
                $employeeID[] = (integer) $item->EmployeeId;
            }
            $employeeID_in = '(' . join(',', $employeeID) . ')';
            $SQL = "SELECT $select "
                    . "FROM (SELECT * FROM $tableName "
                    . "WHERE ApplyDate <= '" . date('Y-m-d', $this->monthForSalary) . "' "
                    . "AND Del_Flag = 0 AND EmployeeId IN $employeeID_in "
                    . "ORDER BY ApplyDate DESC) as temp  "
                    . "GROUP BY EmployeeId";
            $query = $this->db->query($SQL);
            if ($query->num_rows) {
                $temp_ = array();
                $temp = $query->result();
                foreach ($temp as $item) {
                    $temp_[$item->EmployeeId] = $item;
                }
                $temp = $temp_;
            }
        }
        return $temp;
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/20
     * @Description:    check all condition for caculation
     * @Input :
     * @Output :
     */

    function checkCondition() {
        $temp = array(
            'flag' => FALSE,
            'message' => NULL
        );
        $companyCode = $this->session->userdata('CompanyCode');
        $listKey = array(
            'ltc_employees',
            'ltc_conversion_rate',
            'ltc_salary_parameter',
            'ltc_pit_parameter',
            'ltc_pit_rule',
            'ltc_ship_info',
            'ltc_salary_info'
        );
        $listVariable = array();
        foreach ($listKey as $item) {
            $listVariable[$item] = $this->cache->file->get($item . '_' . $companyCode);
        }
        $condition = array(
            'CompanyCode' => $companyCode,
        );
        extract($listVariable);
        $ltc_conversion_rate = ($ltc_conversion_rate) ? $this->getConversionSParametersPParameters($this->table_currencyconversionrate, $condition) : $ltc_conversion_rate;
        $ltc_salary_parameter = ($ltc_salary_parameter) ? $this->getConversionSParametersPParameters($this->table_salaryparameter, $condition) : $ltc_salary_parameter;
        $ltc_pit_parameter = ($ltc_pit_parameter) ? $this->getConversionSParametersPParameters($this->table_pitparameter, $condition) : $ltc_pit_parameter;
        $ltc_pit_rule = ($ltc_pit_rule) ? $this->getPITRules() : $ltc_pit_rule;
        $ltc_salary_info = ($ltc_salary_info) ? $this->getSalaryBonusDependant('EmployeeId, GrossSalary', $this->table_employeesalaryinfo) : $ltc_salary_info;
        if (!$ltc_conversion_rate) {
            $temp['message'] = $this->lang->line('ltc_button_salaryCalculation_active_not_currency_conversion_rate');
        } elseif (!$ltc_salary_parameter) {
            $temp['message'] = $this->lang->line('ltc_button_salaryCalculation_active_not_get_salary_parameter');
        } elseif (!$ltc_pit_parameter) {
            $temp['message'] = $this->lang->line('ltc_button_salaryCalculation_active_not_pit_parameter');
        } elseif (!$ltc_pit_rule) {
            $temp['message'] = $this->lang->line('ltc_button_salaryCalculation_active_not_get_pitcal_culationrule');
        } elseif (count($ltc_employees) != count($ltc_salary_info)) {
            $temp['message'] = $this->lang->line('ltc_button_salaryCalculation_active_not_basic_salarys_info');
        } else {
            $temp['flag'] = TRUE;
        }
        return $temp;
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/20
     * @Description:    caculation net salary for all employee
     * @Input :         (Array) $employeeID
     * @Output :        (Array)
     */

    function salaryCaculations() {
        $temp = array(
            'flag' => FALSE,
            'message' => NULL
        );
        $this->setCacheData();
        $checkCondition = $this->checkCondition();
        if ($checkCondition['flag']) {
            $companyCode = $this->session->userdata('CompanyCode');
            $employees = $this->cache->file->get('ltc_employees_' . $companyCode);
            if ($employees) {
                $this->db->trans_begin();

                /* BEGIN delete old data */
                $employeeID = array();
                foreach ($employees as $item) {
                    $employeeID[] = $item->EmployeeId;
                }
                $condition = array(
                    'Year' => date('Y', $this->monthForSalary),
                    'Month' => date('m', $this->monthForSalary),
                );
                $this->db->where($condition);
                $this->db->where_in('EmployeeId', $employeeID);
                $this->db->delete($this->table_payroll);
                /* END delete old data */

                foreach ($employees as $item) {
                    $time = array(
                        'sp' => ($item->startProbationaryDate == '0000-00-00') ? 0 : strtotime($item->startProbationaryDate),
                        'ep' => ($item->endProbationaryDate == '0000-00-00') ? 0 : strtotime($item->endProbationaryDate),
                        'so' => ($item->startOfficialDate == '0000-00-00') ? 0 : strtotime($item->startOfficialDate),
                        'eo' => ($item->endOfficialDate == '0000-00-00') ? 0 : strtotime($item->endOfficialDate)
                    );
                    $this->divisionCaseCaculation($item->EmployeeId, $time);
                }
                if (($this->db->trans_status() === FALSE)) {
                    $this->db->trans_rollback();
                } else {
                    $this->db->trans_commit();
                    $temp['flag'] = TRUE;
                    $temp['message'] = $this->lang->line('ltc_button_salaryCalculation_active_message_success');
                }
            }
        } else {
            $temp['message'] = $checkCondition['message'];
        }
        return $temp;
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/20
     * @Description:    caculation net salary for one employee
     * @Input :         (integer) $employeeID
     *                  (integer) $sD
     *                  (integer) $ED
     * @Output :
     */

    function salaryCaculation($employeeID = 0, $employeeType = 0, $sD = 0, $eD = 0) {
        /* get month of salary */
        $sm = $this->monthForSalary; /* start month salary */
        $days = cal_days_in_month(1, date('m', $sm), date('Y', $sm));
        $em = strtotime(date('Y-m-' . $days, $sm)); /* end month salary */
        $companyCode = $this->session->userdata('CompanyCode');
        $condition = array(
            'CompanyCode' => $companyCode,
        );

        /* BEGIN actual working hour */
        $employees = $this->cache->file->get('ltc_employees_' . $companyCode);
        if (!$employees) {
            $employees = $this->getEmployee();
        }
        $employee = $employees[$employeeID];
        $actualWorkingHours = $employee->TimeWorking; //(A)
        /* END actual working hour */

        /* BEGIN get conversion rate */
        $conversionRate = $this->cache->file->get('ltc_conversion_rate_' . $companyCode);
        if (!$conversionRate) {
            $conversionRate = $this->getConversionSParametersPParameters($this->table_currencyconversionrate, $condition);
        }
        /* END get conversion rate */

        /* BEGIN salary parameter */
        $salaryParameter = $this->cache->file->get('ltc_salary_parameter' . $companyCode);
        if (!$salaryParameter) {
            $salaryParameter = $this->getConversionSParametersPParameters($this->table_salaryparameter, $condition);
        }
        /* END salary parameter */

        /* BEGIN get basic salary */
        $basicSalarys = $this->cache->file->get('ltc_salary_info_' . $companyCode);
        if (!$basicSalarys) {
            $basicSalarys = $this->getSalaryBonusDependant('EmployeeId, GrossSalary', $this->table_employeesalaryinfo);
        }
        $basicSalary = isset($basicSalarys[$employeeID]->GrossSalary) ? $basicSalarys[$employeeID]->GrossSalary * $conversionRate->Rate : 0; //B
        if ($employeeType == 0) {
            /* Probationary */
            $basicSalary = $basicSalary * $salaryParameter->TrialPeriodRate;
        }
        /* END get basic salary */

        /* BEGIN get basic salary per hour */
        $basicSalaryPerHour = $basicSalary / $actualWorkingHours; //(BH) = (B)/(A)
        /* END get basic salary per hour */

        /* BEGIN Dependant */
        $dependants = $this->cache->file->get('ltc_dependant_info' . $companyCode);
        if (!$dependants) {
            $dependants = $this->getSalaryBonusDependant('EmployeeId, NumberOfDependants', $this->table_employeepitinfo);
        }
        $dependant = isset($dependants[$employeeID]->NumberOfDependants) ? $dependants[$employeeID]->NumberOfDependants : 0; // (C)
        /* END Dependant */

        /* BEGIN Special Leave */
        $specialLeaves = $this->cache->file->get('ltc_special_leave' . $companyCode);
        if (!$specialLeaves) {
            $specialLeaves = $this->getSpecialLeave();
        }
        if (isset($specialLeaves[$employeeID])) {
            $specialDate = $specialLeaves[$employeeID]['specialdate'];
            if (($specialDate >= $sm) && $specialDate <= $em) {
                $specialLeave = $specialLeaves[$employeeID]['actualworkhour'];
            }
        } else {
            $specialLeave = 0;
        }

        /* END Special Leave */

        /* BEGIN E1, E2, E3, F1, F2, G1, G2, H, D1, D3 */
        if (($sm == $sD) && ($em == $eD)) {
            /* BEGIN get employees */
            $employees = $this->cache->file->get('ltc_employees_' . $companyCode);
            if (!$employees) {
                $employees = $this->getEmployee();
            }
            $employee = $employees[$employeeID];
        } else {
            $employee = $this->getTimeCard($employeeID, $sD, $eD);
        }
        /* END get employees */
        $actualAttendant = $employee->ActualAttendant; // (E1)
        $weekdayOvertime = $employee->WeekdayOvertime; // (E2)
        $weekdayLateOvertime = $employee->WeekdayLateOvertime; // (E3)
        $weekendOvertime = $employee->WeekendOvertime; //(F1)
        $weekendLateOvertime = $employee->WeekendLateOvertime; // (F2)
        $holidayOvertime = $employee->HolidayOvertime; // (G1)
        $holidayLateOvertime = $employee->HolidayLateOvertime; // (G2)
        $lateArrivalTime = $employee->LateArrivalTime; // H
        $earlyLeaveTime = $employee->EarlyLeaveTime;
        $paidLeave = $employee->LeaveIsPaid; // D1
        $unPaidLeave = $employee->LeaveNoPaid; // D3
        /* END E1, E2, E3, F1, F2, G1, G2, H */

        /* BEGIN Over Time Allowance */
        $overtimeAllowance = $weekdayOvertime * $salaryParameter->WeekdayOvertimeRate;
        $overtimeAllowance += $weekdayLateOvertime * $salaryParameter->WeekdayLateOvertimeRate;
        $overtimeAllowance += $weekendOvertime * $salaryParameter->WeekendOvertimeRate;
        $overtimeAllowance += $weekendLateOvertime * $salaryParameter->WeekendLateOvertimeRate;
        $overtimeAllowance += $holidayOvertime * $salaryParameter->HolidayOvertimeRate;
        $overtimeAllowance += $holidayLateOvertime * $salaryParameter->HolidayLateOvertimeRate; //(J)
        /* END Over Time Allowance */

        /* BEGIN get bonus */
        $bonus_ = $this->cache->file->get('ltc_bonus_info' . $companyCode);
        if (!$bonus_) {
            $bonus_ = $this->getSalaryBonusDependant('EmployeeId, Bonus', $this->table_employeebonus);
        }
        $bonus = isset($bonus_[$employeeID]->Bonus) ? $bonus_[$employeeID]->Bonus : 0; //K
        /* END get bonus */

        /* BEGIN I, L */
        $realSalary = $basicSalaryPerHour * $actualWorkingHours; //(I)
        $totalSalary = $realSalary + $overtimeAllowance + $bonus; // (L)=(I)+(J)+(K)
        /* END I, L */

        /* BEGIN Deductable for Insurance M1, M2, M3) */
        if ($employeeType) {
            /* Official */
            $salaryForInsuranceCalculation = ($basicSalary > $salaryParameter->MaxSalaryForInsurance) ? $salaryParameter->MaxSalaryForInsurance : $basicSalary; // (L1)
            $socialInsurance = $salaryForInsuranceCalculation * $salaryParameter->SocialInsuranceRate; //(M1)
            $medicalInsurancee = $salaryForInsuranceCalculation * $salaryParameter->MedicalInsuranceRate; //(M2)
            $unemploymentInsurance = $salaryForInsuranceCalculation * $salaryParameter->UnemploymentInsuranceRate; //(M3)
        } else {
            /* Probationary */
            $socialInsurance = 0; //(M1)
            $medicalInsurancee = 0; //(M2)
            $unemploymentInsurance = 0; //(M3)
        }
        $deductableForInsurance = $socialInsurance + $medicalInsurancee + $unemploymentInsurance; /* M = M1+ M2 + M3 */
        /* END Deductable for Insurance M1, M2, M3  */

        /* BEGIN PIT parameter */
        $pitParameter = $this->cache->file->get('ltc_pit_parameter_' . $companyCode);
        if (!$pitParameter) {
            $pitParameter = $this->getConversionSParametersPParameters($this->table_pitparameter, $condition);
        }
        /* END PIT parameter */

        /* BEGIN Deduction For Dependants */
        $deductionForDependants = $pitParameter->EmployeeDeductionAmount + $dependant * $pitParameter->DeductionPerDependant; // (N)
        /* END Deduction For Dependants */

        /* BEGIN PIT parameter, and PIT Rule */
        $temp = $totalSalary - $deductableForInsurance - $deductionForDependants;
        $incomeForPITCalculation = ($temp >= 0) ? $temp : 0; // (O)=(L)-(M)-(N)
        if ($employeeType) {
            /* Official */
            $personalIncomeTax = $this->getPersonalIncomeTax($incomeForPITCalculation); //(N1)
        } else {
            /* Probationary */
            $probationaryTax = $this->config->item('employeeTypeTax');
            if (!$probationaryTax || $probationaryTax > 1 || $probationaryTax < 0) {
                $probationaryTax = 0.1;
            }
            $personalIncomeTax = $incomeForPITCalculation * $probationaryTax; //(N1)
        }
        /* END PIT parameter, and PIT Rule */

        /* BEGIN NetSalary */
        $netSalary = $totalSalary - $deductableForInsurance - $personalIncomeTax; //(Q)
        /* END NetSalary */

        $payroll = array(
            'EmployeeId' => $employeeID,
            'EmployeeType' => $employeeType,
            'Year' => date('Y', $sm),
            'Month' => date('m', $sm),
            'ActualWorkingHours' => $actualWorkingHours, // (A)
            'BasicSalary' => $basicSalary, // (B)
            'BasicSalaryPerHour' => $basicSalaryPerHour, //(Bh)
            'Dependants' => $dependant, // (C)
            'PaidLeave' => $paidLeave, // (D1)
            'SpecialLeave' => $specialLeave, // (D2)
            'UnpaidLeave' => $unPaidLeave, //(D3)
            'ActualAttendant' => $actualAttendant, // (E1)
            'WeekdayOvertime' => $weekdayOvertime, // (E2)
            'WeekdayLateOvertime' => $weekdayLateOvertime, // (E3)
            'WeekendOvertime' => $weekendOvertime, //(F1)
            'WeekendLateOvertime' => $weekendLateOvertime, // (F2)
            'HolidayOvertime' => $holidayOvertime, // (G1)
            'HolidayLateOvertime' => $holidayLateOvertime, // (G2)
            'LateArrivalTime' => $lateArrivalTime, // H
            'EarlyLeaveTime' => $earlyLeaveTime,
            'OvertimeAllowance' => $overtimeAllowance, //(J)
            'Bonus' => $bonus, // (K)
            'SocialInsurance' => $socialInsurance, //(M1)
            'MedicalInsurance' => $medicalInsurancee, // (M2)
            'UnemploymentInsurance' => $unemploymentInsurance, // (M3)
            'DeductionForDependants' => $deductionForDependants, // (N)
            'IncomeForPITCalculation' => $incomeForPITCalculation, // (O)
            'PersonalIncomeTax' => $personalIncomeTax, //(N1)
            'NetSalary' => $netSalary, //(Q)
        );

        $this->db->insert($this->table_payroll, $payroll);
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/20
     * @Description:    caculation net salary for all employee
     * @Input :         (Array) $employeeID
     * @Output :        (Array)
     */

    function getTimeCard($employeeID = 0, $sD = 0, $eD = 0) {
        $sD_ = date('Y-m-d', $sD);
        $eD_ = date('Y-m-d', $eD);
        $select = 'SUM(WeekdayNormalTime) as ActualAttendant'
                . ', SUM(WeekdayOvertime) as WeekdayOvertime'
                . ', SUM(WeekdayLateOvertime) as WeekdayLateOvertime'
                . ', SUM(WeekendOvertime) as WeekendOvertime'
                . ', SUM(WeekendLateOvertime) as WeekendLateOvertime'
                . ', SUM(HolidayOvertime) as HolidayOvertime'
                . ', SUM(HolidayLateOvertime) as HolidayLateOvertime'
                . ', SUM(LateArrival) as LateArrivalTime'
                . ', SUM(EarlyLeave) as EarlyLeaveTime'
                . ', SUM(LeaveIsPaid) as LeaveIsPaid'
                . ', SUM(LeaveNoPaid) as LeaveNoPaid'
                . ', SUM(ActualWorkHours) as sumActualWorkHours';
        $condition = array(
            'select' => $select,
            'where' => array(
                'EmployeeId' => (integer) $employeeID,
                'Date >=' => $sD_,
                'Date <=' => $eD_,
            ),
            'first' => TRUE,
            'group_by' => array('EmployeeId'),
            'disable_del' => TRUE,
        );
        return $this->selectDB($this->table_name, $condition);
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/20
     * @Description:    division case caculation follow Probationary and Probationary
     * @Input :         (integer) $employeeID
     *                  (array) $time
     * @Output :
     */

    function divisionCaseCaculation($employeeID = 0, $time = array()) {
        $sm = $this->monthForSalary; /* start month salary */
        $days = cal_days_in_month(1, date('m', $sm), date('Y', $sm));
        $em = strtotime(date('Y-m-' . $days, $sm)); /* end month salary */
        $sp = $time['sp']; /* start Probationary Date */
        $ep = $time['ep']; /* end Probationary Date */
        $so = $time['so']; /* start Official Date */
        $eo = $time['eo']; /* end Official Date */
        if ($sp && $ep && $so && $eo) {
            /* BEGIN four stone */
            if (($so < $sm) && ($eo >= $sm) && ($eo <= $em)) {
                $this->salaryCaculation($employeeID, 1, $sm, $eo); //(1)
            } elseif (($so >= $sm) && ($eo <= $em) && ($ep < $sm)) {
                $this->salaryCaculation($employeeID, 1, $so, $eo); //(2)
            } elseif (($so >= $sm) && ($eo <= $em) && ($ep >= $sm) && ($sp < $sm)) {
                $this->salaryCaculation($employeeID, 0, $sm, $ep); //(3)
                $this->salaryCaculation($employeeID, 1, $so, $eo); //(3)
            } elseif (($sp >= $sm) && ($eo <= $em)) {
                $this->salaryCaculation($employeeID, 0, $sp, $ep); //(4)
                $this->salaryCaculation($employeeID, 1, $so, $eo); //(4)
            } elseif (($so <= $em) && ($so >= $sm) && ($eo > $em) && ($ep < $sm)) {
                $this->salaryCaculation($employeeID, 1, $so, $em); //(5)
            } elseif (($so <= $em) && ($so >= $sm) && ($eo > $em) && ($ep >= $sm) && ($sp < $sm)) {
                $this->salaryCaculation($employeeID, 0, $sm, $ep); //(6)
                $this->salaryCaculation($employeeID, 1, $so, $em); //(6)
            } elseif (($so <= $em) && ($so >= $sm) && ($eo > $em) && ($sp >= $sm)) {
                $this->salaryCaculation($employeeID, 0, $sp, $ep); //(7)
                $this->salaryCaculation($employeeID, 1, $so, $em); //(7)
            } elseif (($so > $em) && ($sp < $sm) && ($ep >= $sm) && ($ep <= $em)) {
                $this->salaryCaculation($employeeID, 0, $sm, $ep); //(8)
            } elseif (($so > $em) && ($sp >= $sm) && ($ep <= $em)) {
                $this->salaryCaculation($employeeID, 0, $sp, $ep); //(9)
            } elseif (($so > $em) && ($sp >= $sm) && ($sp <= $em) && ($ep > $em)) {
                $this->salaryCaculation($employeeID, 0, $sp, $em); //(10)
            } elseif (($sp < $sm) && ($ep > $em)) {
                $this->salaryCaculation($employeeID, 0, $sm, $em); //(11)
            } elseif (($so < $sm) && ($eo > $em)) {
                $this->salaryCaculation($employeeID, 1, $sm, $em); //(12)
            }
            /* END four stone */
        } elseif ($sp && $ep && $so) {
            /* BEGIN three stone */
            if (($sp < $sm) && ($ep >= $sm) && ($ep <= $em) && ($so <= $em)) {
                $this->salaryCaculation($employeeID, 0, $sm, $ep); //(1)
                $this->salaryCaculation($employeeID, 1, $so, $em); //(1)
            } elseif (($sp < $sm) && ($ep >= $sm) && ($ep <= $em) && ($so > $em)) {
                $this->salaryCaculation($employeeID, 0, $sm, $ep); //(2)
            } elseif (($sp >= $sm) && ($ep <= $em) && ($so <= $em)) {
                $this->salaryCaculation($employeeID, 0, $sp, $ep); //(3)
                $this->salaryCaculation($employeeID, 1, $so, $em); //(3)
            } elseif (($sp >= $sm) && ($ep <= $em) && ($so > $em)) {
                $this->salaryCaculation($employeeID, 0, $sp, $ep); //(4)
            } elseif ($so < $sm) {
                $this->salaryCaculation($employeeID, 1, $sm, $em); //(5)
            } elseif (($ep < $sm) && ($so <= $em)) {
                $this->salaryCaculation($employeeID, 1, $so, $em); //(6)
            } elseif (($sp < $sm) && ($ep > $em)) {
                $this->salaryCaculation($employeeID, 0, $sm, $em); //(7)
            } elseif (($sp <= $em) && ($sp >= $sm) && ($ep >= $em)) {
                $this->salaryCaculation($employeeID, 0, $sp, $em); //(8)
            }
            /* END three stone */
        } elseif ($sp && $ep) {
            /* BEGIN two stone */
            if (($sp < $sm) && ($ep >= $sm) && ($ep <= $em)) {
                $this->salaryCaculation($employeeID, 0, $sm, $ep); //(1)
            } elseif (($sp >= $sm) && ($ep <= $em)) {
                $this->salaryCaculation($employeeID, 0, $sp, $ep); //(2)
            } elseif (($sp <= $em) && ($sp >= $sm) && ($ep > $em)) {
                $this->salaryCaculation($employeeID, 0, $sp, $em); //(3)
            } elseif (($sp < $sm) && ($ep > $em)) {
                $this->salaryCaculation($employeeID, 0, $sm, $em); //(4)
            }
            /* END two stone */
        }
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/22
     * @Description:    get Personal Income Tax
     * @Input :         (Integer) $PITParameterID, float $incomeForPITCalculation=0;
     * @Output :        (float) Income tax object/false
     */

    function getPersonalIncomeTax($incomeForPIT = 0) {
        $temp = 0;
        $companyCode = $this->session->userdata('CompanyCode');
        $pitRule = $this->cache->file->get('ltc_pit_rule_' . $companyCode);
        if (!$pitRule) {
            $pitRule = $this->getPITRules();
        }
        if (count($pitRule) && $incomeForPIT > 0) {
            foreach ($pitRule as $item) {
                if (($incomeForPIT < $item->AssociatedAmount)) {
                    $temp = $incomeForPIT * $item->Rate - $temp;
                    break;
                } else {
                    $temp = $item->AssociatedAmount * $item->Rate - $temp;
                }
            }
        }
        return $temp;
    }

    /*
     * @author :        Quyet
     * @created:        2014/8/22
     * @Description:    get array special day leave in month
     * @Input :
     * @Output :        (array)
     */

    function getSpecialLeave() {
        $temp = array();
        $sm = $this->monthForSalary; /* start month salary */
        $days = cal_days_in_month(1, date('m', $sm), date('Y', $sm));
        $em = strtotime(date('Y-m-' . $days, $sm)); /* end month salary */
        $condition = array(
            'select' => 'a.EmployeeId, a.SpecicalDate, b.ActualWorkHours',
            'where' => "`a`.`SpecicalDate` = `b`.`Date` AND `Date` >='" . date('Y-m-d', $sm) . "' AND `Date` <='" . date('Y-m-d', $em) . "'",
            'join' => array(
                array('table' => $this->table_name . ' as b', 'condition' => 'a.EmployeeId = b.EmployeeId', 'type' => 'inner')
            )
        );
        $result = $this->selectDB($this->table_employeeleaveinfo . ' as a', $condition);
        if (count($result)) {
            foreach ($result as $item) {
                $temp[$item->EmployeeId]['specialdate'] = strtotime($item->SpecicalDate);
                $temp[$item->EmployeeId]['actualworkhour'] = $item->ActualWorkHours;
            }
        }
        return $temp;
    }

    /*
     * @author :        Quyet
     * @created:        4/7/2014
     * @Description:    Set month, year for calculation
     * @Input :         void
     * @Output :        void
     */

    function setMonthYearForCalculation() {
        $month = $this->input->post('cmbMonth');
        $year = $this->input->post('cmbYear');
        $range = $this->config->item('rangeYear');
        $minYear = $range['minYear'];
        $maxYear = $range['maxYear'];
        if ($month > 0 && $month <= 12 && $year >= $minYear && $year <= $maxYear) {
            $data = array(
                'cmbMonth' => $month,
                'cmbYear' => $year,
            );
            $this->session->set_userdata($data);
        }
    }

}

?>
